package com.mycompany.myapp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class TodoDAO {
	public static int createTodo(Todo newTodo) throws ClassNotFoundException, SQLException{
        Connection c = getConnection();
        PreparedStatement ps = c.prepareStatement("insert into todos(pky, title, content) values(?,?,?)");
        ps.setLong(1, newTodo.getPky());
        ps.setString(2, newTodo.getTitle());
        ps.setString(3, newTodo.getContent());             
        ps.executeUpdate(); 
        ps.close();
        c.close();
        return 1;
	}
	public Todo get(int pky) throws ClassNotFoundException, SQLException{
        Connection c = getConnection();
        PreparedStatement ps = c.prepareStatement("select * from todos where pky=?");
        ps.setLong(1, pky);            
        ResultSet rs = ps.executeQuery();
        rs.next();
        Todo todo = new Todo(rs.getInt("pky"), rs.getString("title"),rs.getString("content"));
        rs.close();
        ps.close();
        c.close();       
        return todo;
	}  
	public static int removeTodo(int pky) throws ClassNotFoundException, SQLException {
		Connection c = getConnection();
		PreparedStatement ps = c.prepareStatement("delete from todos where pky=?");
		ps.setLong(1, pky);
		ps.execute();
		ps.close();
		c.close();
		return 1;
	}
	public static int modifyTodo(Todo newTodo) throws ClassNotFoundException, SQLException{
		Connection c = getConnection();
		PreparedStatement ps = c.prepareStatement("update todos set title = '" + newTodo.getTitle() + "', content = '" + newTodo.getContent() + "' where id=?");
		ps.setLong(1, newTodo.getId());
		ps.executeUpdate();
		ps.close();
		c.close();
		return 1;
	}
	private static Connection getConnection() throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.jdbc.Driver");
        Connection c = DriverManager.getConnection("jdbc:mysql://localhost/todotest", "root", "als984");
        return c;
	}

}
